<?php
/**
 * 导出数据到EXCEL表格
 * 
 * 使用PHPExcel，支持CodeIgniter 2.2.x及以上
 * 
 * @author Yuan Xibin <yxb_unix@163.com>
 * @license	http://opensource.org/licenses/MIT	MIT License
 * @version 1.0.0 2015-06-24
 * @filesource
 */
class Outexcel{
    
    /**
     * 标题
     * @var string 
     */
    protected $title = '';
    
    /**
     * 列名
     * @var array 
     */
    protected $fields = array();
    
    /**
     * 列的数量，即列名数组的第一维元素个数
     * @var string 
     */
    private $cols = 0;
    
    /**
     * 信息数组
     * @var array
     */
    protected $info = array();
    
    /**
     * PHPExcel对象
     * @var object 
     */
    private $objPHPExcel = null;


    /**
     * 对象初始化设置成员变量。
     * 
     * @param array $data
     * title=>标题<br>
     * fields=>列名二维数组。<b>第二维键名0：信息数组键名；1：列名</b><br>
     * info=>信息数组<br>
     */
    public function __construct($data = array()) {
        if(isset($data['title']) && $data['title'] != '')
            $this->title = $data['title'];
        
        if(isset($data['fields']) && is_array($data['fields']) && count($data['fields']) > 0){
            $this->fields = $data['fields'];
            $this->cols = count($data['fields']);
        }
        
        if(isset($data['info']) && is_array($data['info']) && count($data['info']) > 0)
            $this->info = $data['info'];
        
        $CI = &get_instance();
        $CI->load->library("PHPExcel");	
	$CI->load->library("PHPExcel/IOFactory");
        
        $this->objPHPExcel = new PHPExcel();
    }
    
    /**
     * 把数字（0-25）转换为字母A-Z
     * @param int $num 数字0-25
     * @return string 字母A-Z
     */
    private function get_zm($num = 0){
        $num = (int)$num;
        if($num < 0 || $num > 25){
            return '';
        }
        
        return chr(ord('A') + $num);
    }
    
    public function output(){
        $this->objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
        $this->objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
        
        //设置第一行合并单元格
        $this->objPHPExcel->getActiveSheet()->mergeCells('A1:'.$this->get_zm($this->cols - 1).'1');
        
        //设置标题
        $this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0, 1, $this->title);
        $objStyleA1 = $this->objPHPExcel->getActiveSheet()->getStyle('A1');
        $objFontA1 = $objStyleA1->getFont();
        $objFontA1->setName('宋体');
        $objFontA1->setSize(14);
        $objFontA1->setBold(true);

        //设置列名
        $i_fields = 0;
        foreach ($this->fields as $fields_v){
            $this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($i_fields, 2, $fields_v[1]);
            $i_fields++;
        }
        
        //设置列格式
        for($b=0;$b<$this->cols;$b++){
                $z=$this->get_zm($b);
                if($z == '')continue;
                for($a=1;$a<=2;$a++){
                        $this->objPHPExcel->getActiveSheet()->getStyle($z.$a)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );   
                        $this->objPHPExcel->getActiveSheet()->getStyle($z.$a)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );   
                        $this->objPHPExcel->getActiveSheet()->getStyle($z.$a)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); 
                        $this->objPHPExcel->getActiveSheet()->getStyle($z.$a)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); 
                        $this->objPHPExcel->getActiveSheet()->getStyle($z.$a)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

                }
                $this->objPHPExcel->getActiveSheet()->getColumnDimension($z)->setWidth(15);
        }
        
        //设置数据
        $r = 3;
        foreach($this->info as $info_v){
            foreach($this->fields as $f_k=>$f_v){
                $this->objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($f_k, $r, $info_v[$f_v[0]]);
                $this->objPHPExcel->getActiveSheet()->getStyle($this->get_zm($f_k).$r)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );
                $this->objPHPExcel->getActiveSheet()->getStyle($this->get_zm($f_k).$r)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN );   
                $this->objPHPExcel->getActiveSheet()->getStyle($this->get_zm($f_k).$r)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); 
                $this->objPHPExcel->getActiveSheet()->getStyle($this->get_zm($f_k).$r)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN ); 
                $this->objPHPExcel->getActiveSheet()->getStyle($this->get_zm($f_k).$r)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }
            $r++;
        }
        
        $this->objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);
        $this->objPHPExcel->setActiveSheetIndex(0);
        $objWriter = IOFactory::createWriter($this->objPHPExcel, 'Excel5');

        //发送标题强制用户下载文件
        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="excel_'.time().'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
}